package com.example.springboot1.api;

import cn.afterturn.easypoi.word.WordExportUtil;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.enums.WriteDirectionEnum;
import com.alibaba.excel.support.ExcelTypeEnum;
import com.alibaba.excel.util.FileUtils;
import com.alibaba.excel.write.merge.LoopMergeStrategy;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.alibaba.excel.write.metadata.fill.FillWrapper;
import com.example.springboot1.api.sele.ImageData;
import com.example.springboot1.api.sele.MyExcel;
import com.example.springboot1.entity.DutyExcelBo;
import com.example.springboot1.entity.FillData;
import com.example.springboot1.entity.FillDataList;
import com.example.springboot1.entity.NormalData;
import com.example.springboot1.tools.CustomMergeStrategy;
import com.example.springboot1.utils.BussinessException;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.CrossOrigin;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.mvc.method.annotation.StreamingResponseBody;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URL;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author ckj
 * @description TODO
 * @date 2022-06-23 18:21
 */

@RestController
public class FillExcelApi {


    @GetMapping("/excel")
    public void exportInfo(HttpServletResponse response) throws IOException {

        OutputStream out = null;
        BufferedOutputStream bos = null;
        try {
            InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/dd.xlsx");
//            String templateFileName = "templates" + File.separator + "dd.xlsx";
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("下载后的名称.xlsx", "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);

            //读取Excel
            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(resourceAsStream).build();
            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            //list map 是查询并需导出的数据，并且里面的字段和excel需要导出的字段对应
            // 直接写入Excel数据
//            List<Map> list = new ArrayList<>();
//            Map<String,Object> map = new HashMap<>();
//            excelWriter.fill(list, writeSheet);
//            excelWriter.fill(map, writeSheet);
            FillData fillData = new FillData();
            fillData.setName("张三");
            fillData.setNumber(5.2);
            excelWriter.fill(fillData, writeSheet);
            List<FillData> data = new ArrayList<>();
            data.add(fillData);
            data.add(fillData);
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
            excelWriter.fill(data, fillConfig, writeSheet);

            Map<String, Object> map = new HashMap<>();
            map.put("date", "2019年10月9日13:28:28");
            excelWriter.fill(map, writeSheet);

            excelWriter.finish();
            bos.flush();

        } catch (Exception e) {

        }
    }

    @GetMapping("/normal")
    public void exportNormal(HttpServletResponse response) throws IOException {

        OutputStream out = null;
        BufferedOutputStream bos = null;
        try {
            InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/normal1.xlsx");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("下载后的名称.xlsx", "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);

            NormalData fillData = new NormalData();
            fillData.setName("张三");
            fillData.setContents("测试");
            NormalData fillData1 = new NormalData();
            fillData1.setName("张三");
            fillData1.setContents("测试2");
            NormalData fillData3 = new NormalData();
            fillData3.setName("王五");
            fillData3.setContents("测试2");
            List<NormalData> data = new ArrayList<>();
            data.add(fillData);
            data.add(fillData1);
            data.add(fillData3);
            //读取Excel
            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(resourceAsStream).excelType(ExcelTypeEnum.XLSX).build();

            WriteSheet writeSheet = EasyExcel.writerSheet()
                    .registerWriteHandler(new CustomMergeStrategy(data.stream().map(NormalData::getName).collect(Collectors.toList()), 0))
                    .build();


//            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            excelWriter.fill(data, writeSheet);
            Map<String, Object> map = new HashMap<>();
            map.put("date", "2019年10月9日13:28:28");
            excelWriter.fill(map, writeSheet);

            excelWriter.finish();
            bos.flush();

        } catch (Exception e) {
            System.out.println(1111);
        }
    }

    @GetMapping("/merge")
    public void test(HttpServletResponse response) {

        OutputStream out = null;
        BufferedOutputStream bos = null;
        String url = "http://10.192.34.84:8075/webroot/decision/view/report?viewlet=gsyj%252Fzbj%252Fzbjjld.cpt&format=excel";
        String url1 = "http://10.192.34.84:8075/webroot/decision/view/report?viewlet=gsyj%252Fzbj%252Fzbjsqb.cpt&format=excel";
        try {
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("下载后的名称.xlsx", "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);
            URL http = new URL(url);
            InputStream inputStream = http.openStream();
//            EasyExcel.read(inputStream);
            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(inputStream).excelType(ExcelTypeEnum.XLSX).build();
            WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet1").build();
            Map<String, Object> map = new HashMap<>();
            map.put("date", "2019年10月9日13:28:28");
            excelWriter.fill(map, writeSheet);
            URL http1 = new URL(url1);
            InputStream inputStream1 = http1.openStream();
            excelWriter = EasyExcel.write(bos).withTemplate(inputStream1).excelType(ExcelTypeEnum.XLSX).build();
            WriteSheet writeSheet1 = EasyExcel.writerSheet(1, "sheet2").build();
            excelWriter.fill(map, writeSheet1);
            excelWriter.finish();
            bos.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }


    @GetMapping("/test2")
    public void test2(HttpServletResponse response) throws IOException {

        OutputStream out = null;
        BufferedOutputStream bos = null;

        try {
            InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/record.xlsx");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("下载后的名称.xlsx", "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);

            NormalData fillData = new NormalData();
            fillData.setName("1");
            fillData.setContents("测试");
            NormalData fillData1 = new NormalData();
            fillData1.setName("1");
            fillData1.setContents("测试2");
            NormalData fillData3 = new NormalData();
            fillData3.setName("王五");
            fillData3.setContents("测试2");
            List<NormalData> data = new ArrayList<>();
            data.add(fillData);
            data.add(fillData1);
            data.add(fillData3);

            List<FillDataList> dataLists = new ArrayList<>();
            FillDataList fillDataList = new FillDataList();
            fillDataList.setRecordName("dddd");
            //读取Excel
            dataLists.add(fillDataList);
            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(resourceAsStream).excelType(ExcelTypeEnum.XLSX).build();

            WriteSheet writeSheet = EasyExcel.writerSheet()
                    .registerWriteHandler(new CustomMergeStrategy(data.stream().map(NormalData::getName).collect(Collectors.toList()), 0))
                    .build();


//            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            excelWriter.fill(data, writeSheet);

//            excelWriter.fill(fillDataList, writeSheet);

            excelWriter.finish();
            bos.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }


    @GetMapping("/test3")
    public void test3(HttpServletResponse response) throws IOException {

        OutputStream out = null;
        BufferedOutputStream bos = null;

        try {
            InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/duty.xlsx");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("下载后的名称.xlsx", "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);


            NormalData fillData = new NormalData();
            fillData.setName("张三1");
            fillData.setContents("测试");
            NormalData fillData1 = new NormalData();
            fillData1.setName("张三2");
            fillData1.setContents("测试2");
            NormalData fillData3 = new NormalData();
            fillData3.setName("王五3");
            fillData3.setContents("测试2");
            List<NormalData> data = new ArrayList<>();
            data.add(fillData);
            data.add(fillData1);
            data.add(fillData3);

            List<FillDataList> dataLists = new ArrayList<>();
            FillDataList fillDataList = new FillDataList();
            fillDataList.setRecordName("dddd");
            FillDataList fillDataList1 = new FillDataList();
            fillDataList1.setRecordName("d4");
            FillDataList fillDataList2 = new FillDataList();
            fillDataList2.setRecordName("dddd777");
            //读取Excel
            dataLists.add(fillDataList);
            dataLists.add(fillDataList1);
            dataLists.add(fillDataList2);
            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(resourceAsStream).excelType(ExcelTypeEnum.XLSX).build();

//            WriteSheet writeSheet = EasyExcel.writerSheet()
//                    .registerWriteHandler(new CustomMergeStrategy(data.stream().map(NormalData::getName).collect(Collectors.toList()), 0))
//                    .build();


            WriteSheet writeSheet = EasyExcel.writerSheet().build();
            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
//            excelWriter.fill(new FillWrapper("data1", data),fillConfig, writeSheet);
            data.forEach(x -> {

                excelWriter.fill(new FillWrapper("data1", data), writeSheet);

            });

//
//            Map<String, Object> map = new HashMap<String, Object>();
//            //map.put("date", "2019年10月9日13:28:28");
//            map.put("key1", 1);
//            map.put("key2",2);
//            map.put("key3", 3);
//
//            excelWriter.fill(map, writeSheet);
            excelWriter.finish();
            bos.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }


    @GetMapping("/duty")
    public void duty(HttpServletResponse response) throws IOException {

        OutputStream out = null;
        BufferedOutputStream bos = null;

        try {
            List<DutyExcelBo> result = new ArrayList<>();
            DutyExcelBo dutyExcelBo = new DutyExcelBo();
            dutyExcelBo.setSort("测试");
            dutyExcelBo.setDutyUserName("jack");
            dutyExcelBo.setDutyDate("10.8");
            DutyExcelBo dutyExcelBo1 = new DutyExcelBo();
            dutyExcelBo1.setSort("测试");
            dutyExcelBo1.setDutyUserName("jack");
            dutyExcelBo1.setDutyDate("10.9");
            result.add(dutyExcelBo1);
            InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/record.xlsx");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String name = "值班记录.xlsx";
            String fileName = URLEncoder.encode(name, "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);

            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(resourceAsStream).excelType(ExcelTypeEnum.XLSX).build();

            WriteSheet writeSheet = EasyExcel.writerSheet()
                    .registerWriteHandler(new CustomMergeStrategy(result.stream().map(DutyExcelBo::getSort).collect(Collectors.toList()), 0))
                    .build();
            excelWriter.fill(result, writeSheet);

            Map<String, Object> map = new HashMap<>();

            map.put("title", "2023-11");
            excelWriter.fill(map, writeSheet);
            excelWriter.finish();
            bos.flush();

            excelWriter.finish();
            bos.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }


    }


    @GetMapping("/demo")
    public void demo(HttpServletResponse response) {
        OutputStream out = null;
        BufferedOutputStream bos = null;

        try {
            InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/record.xlsx");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String fileName = URLEncoder.encode("下载后的名称.xlsx", "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);

            NormalData fillData = new NormalData();
            fillData.setName("1");
            fillData.setContents("测试");
            NormalData fillData1 = new NormalData();
            fillData1.setName("1");
            fillData1.setContents("测试2");
            NormalData fillData3 = new NormalData();
            fillData3.setName("王五");
            fillData3.setContents("测试2");
            List<NormalData> data = new ArrayList<>();
            data.add(fillData);
            data.add(fillData1);
            data.add(fillData3);

            List<FillDataList> dataLists = new ArrayList<>();
            FillDataList fillDataList = new FillDataList();
            fillDataList.setRecordName("dddd");
            //读取Excel
            dataLists.add(fillDataList);
//            ExcelWriter excelWriter = EasyExcel.write(bos).excelType(ExcelTypeEnum.XLSX).build();

//            WriteSheet writeSheet = EasyExcel.writerSheet()
//                    .registerWriteHandler(new CustomMergeStrategy(data.stream().map(NormalData::getName).collect(Collectors.toList()), 0))
//                    .build();

            LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
            // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
            EasyExcel.write(bos, NormalData.class)
                    .sheet()
                    .head(generateHead())
                    .doWrite(data);

//            excelWriter.fill(data, writeSheet);


//            excelWriter.finish();
            bos.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static List<List<String>> generateHead() {

        List<List<String>> headList2D = new ArrayList<>();

        headList2D.add(Arrays.asList("运营值班表", "序号", "序号")); // 一列
        headList2D.add(Arrays.asList("运营值班表", "值班人员姓名", "值班人员姓名"));
        headList2D.add(Arrays.asList("运营值班表", "值班日期", "工作日"));
        headList2D.add(Arrays.asList("运营值班表", "值班日期", "周末、节假日"));
        headList2D.add(Arrays.asList("运营值班表", "签字", "签字"));

        return headList2D;
    }

    @CrossOrigin
    @GetMapping("/work")
    public ResponseEntity<StreamingResponseBody> work() {
        try {
            StreamingResponseBody streamingResponseBody = new StreamingResponseBody() {

                @Override

                public void writeTo(OutputStream out) throws IOException {
                    InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/dd.xlsx");
                    //读取Excel
                    ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(resourceAsStream).build();
                    WriteSheet writeSheet = EasyExcel.writerSheet().build();

                    FillData fillData = new FillData();
                    fillData.setName("张三");
                    fillData.setNumber(5.2);
                    excelWriter.fill(fillData, writeSheet);
                    List<FillData> data = new ArrayList<>();
                    data.add(fillData);
                    data.add(fillData);
                    FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
                    excelWriter.fill(data, fillConfig, writeSheet);

                    Map<String, Object> map = new HashMap<>();
                    map.put("date", "2019年10月9日13:28:28");
                    excelWriter.fill(map, writeSheet);

                    excelWriter.finish();

                }

            };
//            ByteArrayOutputStream out = new ByteArrayOutputStream();
//
//            InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/dd.xlsx");
//            //读取Excel
//            ExcelWriter excelWriter = EasyExcel.write(out).withTemplate(resourceAsStream).build();
//            WriteSheet writeSheet = EasyExcel.writerSheet().build();
//
//            FillData fillData = new FillData();
//            fillData.setName("张三");
//            fillData.setNumber(5.2);
//            excelWriter.fill(fillData, writeSheet);
//            List<FillData> data = new ArrayList<>();
//            data.add(fillData);
//            data.add(fillData);
//            FillConfig fillConfig = FillConfig.builder().direction(WriteDirectionEnum.HORIZONTAL).build();
//            excelWriter.fill(data, fillConfig, writeSheet);
//
//            Map<String, Object> map = new HashMap<>();
//            map.put("date", "2019年10月9日13:28:28");
//            excelWriter.fill(map, writeSheet);
//
//            excelWriter.finish();
//            byte[] bytes = out.toByteArray();


            String fileName = "xx.xlsx";
            HttpHeaders headers = new HttpHeaders();

//            String userAgent = HttpKit.getRequest().getHeader("User-Agent");
//            if (userAgent.toLowerCase().indexOf("trident") > -1
//                    || userAgent.toLowerCase().indexOf("edge") > -1) {// IE，Edge浏览器
//                fileName = URLEncoder.encode(fileName, "UTF-8");
//            } else {
//                fileName = new String(fileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1);
//            }
            headers.setContentType(MediaType.valueOf("application/vnd.ms-excel"));
            headers.add("Content-Disposition", "attachment; filename=\"" + fileName + "\"");
            HttpStatus httpStatus = HttpStatus.OK;

            return new ResponseEntity(streamingResponseBody, headers, httpStatus);
        } catch (Exception e) {
            throw new BussinessException("文件下载失败");
        }
    }

    @GetMapping("/clock")
    public void getClock(HttpServletResponse response) {
        OutputStream out;
        BufferedOutputStream bos;
        try {
            List<MyExcel> data = new ArrayList<>();

            List<ImageData> list = new ArrayList<ImageData>();
            ImageData imageData = new ImageData();
            list.add(imageData);
            InputStream resourceAsStream = this.getClass().getClassLoader().getResourceAsStream("templates/clock.xlsx");
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String name = String.format("曾家岩%s打卡记录%s.xlsx", "1", "2");
            String fileName = URLEncoder.encode(name, "utf-8");
            response.setHeader("Content-disposition", "attachment; filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));

            out = response.getOutputStream();
            bos = new BufferedOutputStream(out);
//            ExcelWriter excelWriter = EasyExcel.write(bos).withTemplate(resourceAsStream).build();
//            WriteSheet writeSheet = EasyExcel.writerSheet().build();
//            excelWriter.fill(data, writeSheet);
//            excelWriter.finish();

//            data.add(new MyExcel().setImageStrings(list.subList(0, 2)).setImageUrls(urls.subList(0, 1)).setName("赵六").setAge("12"));
//            excelList.add(new MyExcel().setImageStrings(list.subList(0, 1)).setImageUrls(urls.subList(0, 2)).setName("纳兹").setAge("12"));
            //异常string和url类型图片地址
            data.add(new MyExcel().setImageUrls(Arrays.asList(new URL("https://pics6.baidu.com/feed/d50735fae6cd7b896f493d3b44c234abd8330e57.jpeg@f_auto?token=20df77fa0873f4faa61c7442a2cc93ba"))).setName("纳兹").setAge("12"));
            //无图片
//            excelList.add(new MyExcel().setImageStrings(list.subList(0, 0)).setImageUrls(urls.subList(0, 0)).setName("纳兹").setAge("12"));


//            EasyExcel.write(bos, MyExcel.class)
//                    .registerWriteHandler(new CustomImageModifyHandler()).sheet().doWrite(data);

            imageData.setUrl(new URL(
                    "https://pics6.baidu.com/feed/d50735fae6cd7b896f493d3b44c234abd8330e57.jpeg@f_auto?token=20df77fa0873f4faa61c7442a2cc93ba"));
            imageData.setString("D:\\file\\1.jpg");
            imageData.setByteArray(FileUtils.readFileToByteArray(new File("D:\\file\\1.jpg")));
//            EasyExcel.write(bos, ImageData.class).sheet().doWrite(list);
            EasyExcel.write("D:\\file\\b.xlsx", ImageData.class).sheet().doWrite(list);

            bos.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    @GetMapping("/demoWord")
    public void word(HttpServletRequest request, HttpServletResponse response) {
        Map<String, Object> data = new HashMap<String, Object>();
        String path = this.getClass().getClassLoader().getResource("templates/approvaForm.doc").getPath();

        try {
            XWPFDocument doc = WordExportUtil.exportWord07(path, data);
            String fileName = "党费使用审批表.docx";
            String header = request.getHeader("User-Agent");
            if (header != null && (header.contains("Firefox") || header.contains("firefox"))) {
                throw new RuntimeException("请使用谷歌浏览器下载");
             } else {
                fileName = URLEncoder.encode(fileName, "UTF-8");
            }
            response.setHeader("content-disposition", "attachment; filename=" + fileName);
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            ServletOutputStream outputStream = response.getOutputStream();
            doc.write(outputStream);
            outputStream.close();
            doc.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
